﻿using ARS.Common.Entitys;
using ARS.Common.Helper;
using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace ARS.Common.Service
{
    /// <summary>
    /// 数据操作层 ARS的数据操作层
    /// </summary>
    public class DataRepository
    {

        private PgSqlHelper _PgConnection = null;
        public DataRepository(string pgConnectionStr)
        {
            if (string.IsNullOrEmpty(pgConnectionStr))
                throw new Exception("请输入正确的链接字符串");
            _PgConnection = new PgSqlHelper(pgConnectionStr);
           
        }






        #region   EmailTemplate
        public Tuple<IEnumerable<EmailTemplate>, int> GetEmailTemplateAll(string subject = null, bool? isEnable = null, int pageNumber = 1, int pageSize = 25)
        {
            IList<string> strbu = new List<string>();
            if (!string.IsNullOrEmpty(subject))
            {
                strbu.Add($"name like '%{subject}%'");
            }
            if (isEnable.HasValue)
            {
                strbu.Add($"isenable='{(isEnable.Value ? "t" : "f")}'");
            }
            var data = _PgConnection.GetListByPage<EmailTemplate>("emailtemplate", "", string.Join(" and ", strbu), "id desc", pageNumber, pageSize);
            return data;
        }
        public int AddEmailTemplates(IEnumerable<EmailTemplate> items)
        {
            using (var dbConnection = _PgConnection.PgConnection)
            {
                var result = dbConnection.Execute($"insert into emailtemplate(Name,mailSenderId,sendname,recipients,cc,bcc,subject,body,dataattachmenttype,attachmentsignature,multiplesheet,sheetnames,execinstance,sqlparams,sqlscript,isenable) VALUES(@Name,@mailSenderId,@sendname,@recipients,@cc,@bcc,@subject,@body,@dataattachmenttype,@attachmentsignature,@multiplesheet,@sheetnames,@execinstance,@sqlparams,@sqlscript,@isenable)", items);
                dbConnection.Close();
                return result;
            }
        }

        public int UpdateEmailTemplates(EmailTemplate item)
        {
            using (var dbConnection = _PgConnection.PgConnection)
            {
                item.UpdateTime = DateTime.Now;
                var data = dbConnection.Execute($"update emailtemplate set sheetnames=@sheetnames,dataattachmenttype=@dataattachmenttype,body=@body,multiplesheet=@multiplesheet,isenable=@isenable,updatetime=CURRENT_TIMESTAMP,attachmentsignature=@attachmentsignature,sqlscript=@sqlscript,sqlparams=@sqlparams,subject=@subject,bcc=@bcc,cc=@cc,execinstance=@execinstance,name=@name,mailsenderid=@mailsenderid,sendname=@sendname,recipients=@recipients WHERE id =@id", item);
                dbConnection.Close();
                return data;
            }
        }
        public int UpdateEmailTask(EmailTask item)
        {
            using (var dbConnection = _PgConnection.PgConnection)
            {
                item.UpdateTime = DateTime.Now;
                var data = dbConnection.Execute($"update emailtask set centent=@centent,cronstr=@cronstr,emailtemplateid=@emailtemplateid,name=@name,updatetime=CURRENT_TIMESTAMP WHERE id =@id", item);
                dbConnection.Close();
                return data;
            }
        }
        public int UpdatEmailSender(MailSender item)
        {
            item.Password = StringHelper.Encrypt(item.Password);
            using (var dbConnection = _PgConnection.PgConnection)
            {

                var data=  dbConnection.Execute($"update mailsender set port=@port,authentication=@authentication,password=@password,server=@server,\"user\"=@user,name=@name WHERE id =@id", item);
                dbConnection.Close();
                return data;
            }
        }
        public int AddEmailSender(MailSender item)
        {
            item.Password = StringHelper.Encrypt(item.Password);
            using (var dbConnection = _PgConnection.PgConnection)
            {
                var data = dbConnection.Execute($"insert into mailsender(port,authentication,server,\"user\",name,password) VALUES(@port,@authentication,@server,@user,@name,@password);", item);
                dbConnection.Close();
                return data;
            }
        }
        public int AddSQlInstance(SQlInstance item)
        {
            item.DBstring = StringHelper.Encrypt(item.DBstring);
            using (var dbConnection = _PgConnection.PgConnection)
            {
                var data = dbConnection.Execute($" insert into sqlinstance(dbtype, dbstring, name) VALUES(@dbtype, @dbstring, @name);", item);
                dbConnection.Close();
                return data;
            }
        }
       
        public int UpdatSqlInstance(SQlInstance item)
        {
            item.DBstring = StringHelper.Encrypt(item.DBstring);
            using (var dbConnection = _PgConnection.PgConnection)
            {
                item.UpdateTime = DateTime.Now;
                var data= dbConnection.Execute($"update sqlinstance set dbtype=@dbtype,dbstring=@dbstring,updatetime=CURRENT_TIMESTAMP,name=@name WHERE id =@id", item);
                dbConnection.Close();
                return data;
            }
        }
        /*



             
             */
        public EmailTemplate GetEmailTemplate(int id)
        {
            using (var dbConnection = _PgConnection.PgConnection)
            {
                var data = dbConnection.Query<EmailTemplate>($"SELECT * FROM emailtemplate WHERE id = @Id ", new { Id = id }).FirstOrDefault();
                dbConnection.Close();
                return data;
            }
        }



        #endregion
        #region  SQlInstance
        /// <summary>
        /// 获取
        /// </summary>
        /// <returns></returns>
        public SQlInstance GetSQlInstance(int id)
        {
            using (var dbConnection = _PgConnection.PgConnection)
            {
                var data = dbConnection.Query<SQlInstance>($"SELECT * FROM sqlinstance WHERE id = @Id ", new { Id = id }).FirstOrDefault();
                dbConnection.Close();
                return data;
            }
        }
        public IEnumerable<SQlInstance> GetSQlInstanceAll()
        {
            using (var dbConnection = _PgConnection.PgConnection)
            {
                var data= dbConnection.Query<SQlInstance>($"SELECT * FROM sqlinstance order by id desc");
                dbConnection.Close();
                return data;
            }
        }

        
            public Tuple<IEnumerable<MailSender>, int> GetMailSenderByPage(string name = null, int pageNumber = 1, int pageSize = 25)
        {
           
            IList<string> strbu = new List<string>();
            if (!string.IsNullOrEmpty(name))
            {
                strbu.Add($"name like '%{name}%'");
            }
            //if (dbtype.HasValue)
            //{
            //    strbu.Add($"dbtype='{(int)dbtype.Value}'");
            //}
            var data = _PgConnection.GetListByPage<MailSender>("mailsender", "", string.Join(" and ", strbu), "id desc", pageNumber, pageSize);
            return data;
        }
        public Tuple<IEnumerable<SQlInstance>, int> GetSQlInstanceByPage(string name = null, int pageNumber = 1, int pageSize = 25)
        {
           
            IList<string> strbu = new List<string>();
            if (!string.IsNullOrEmpty(name))
            {
                strbu.Add($"name like '%{name}%'");
            }
            //if (dbtype.HasValue)
            //{
            //    strbu.Add($"dbtype='{(int)dbtype.Value}'");
            //}
            var data = _PgConnection.GetListByPage<SQlInstance>("sqlinstance", "", string.Join(" and ", strbu), "id desc", pageNumber, pageSize);
            return data;
        }
        #endregion
        #region   MailSender
        /// <summary>
        /// 获取发送配置表
        /// </summary>
        /// <returns></returns>
        public MailSender GetMailSender(int id)
        {
            using (var dbConnection = _PgConnection.PgConnection)
            {
                var data = dbConnection.Query<MailSender>($"SELECT * FROM mailsender WHERE id = @Id ", new { Id = id }).FirstOrDefault(); 
                dbConnection.Close();
                return data;
            }
        }
        public IEnumerable< MailSender> GetMailSenderAll()
        {
            using (var dbConnection = _PgConnection.PgConnection)
            {
                var data= dbConnection.Query<MailSender>($"SELECT * FROM mailsender order by id desc");
                dbConnection.Close();
                return data;
            }
        }
        #endregion
        #region  MailContacts
        public IEnumerable<MailContacts> GetMailContacts()
        {
            using (var dbConnection = _PgConnection.PgConnection)
            {
               var data=  dbConnection.Query<MailContacts>($"SELECT * FROM mailcontacts");
                dbConnection.Close();
                return data;
            }
        }
        #endregion



        #region  EmailTask Dao
        public EmailTask GetEmailTask(int id)
        {
            using (var dbConnection = _PgConnection.PgConnection)
            {
                var data= dbConnection.Query<EmailTask>($"SELECT * FROM emailtask WHERE id = @Id ", new { Id = id }).FirstOrDefault();
                dbConnection.Close();
                return data;
            }
        }
        public Tuple<IEnumerable<EmailTask>, int>   GetEmailTaskAll(string name = null, int pageNumber = 1, int pageSize = 25)
        {
            IList<string> strbu = new List<string>();
            if (!string.IsNullOrEmpty(name))
            {
                strbu.Add($"name like '%{name}%'");
            }
           
            var data = _PgConnection.GetListByPage<EmailTask>("emailtask", "", string.Join(" and ", strbu), "id desc", pageNumber, pageSize);
            return data;
        }
        public int AddEmailTasks(IEnumerable<EmailTask> items)
        {
            using (var dbConnection = _PgConnection.PgConnection)
            {
                var result = dbConnection.Execute($"insert into emailtask(name,emailtemplateid,cronstr,centent)VALUES(@name,@emailtemplateid,@cronstr,@centent)", items);
                dbConnection.Close();
                return result;
            }
        }
        #endregion




        #region  emailtask_logs
        public int AddEmailTask_Logs(EmailTask_Logs items)
        {
            using (var dbConnection = _PgConnection.PgConnection)
            {
                var result = dbConnection.Execute($"insert into emailtask_logs(taskid,centent,status)VALUES(@taskid,@centent,@status)", items);
                dbConnection.Close();
                return result;
            }
        }


        /// <summary>
        /// 获取datatable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public Tuple<int,DataTable> GetEmailtask_logs(DateTime start,DateTime end)
        {
            var sqltemp = @"
with tab as(
select cast(generate_series('v_start'::date,'v_end'::date, '1 days') as date) t
),
tab1 as (select  cast(createtime  as date) dates,count(1) cts from emailtask_logs el where status=0 group by cast(createtime  as date)
)
select to_char( tab.t,'yyyy-MM-dd') 日期, coalesce (cts,0) 数量  from tab left join tab1 on tab.t=tab1.dates order by tab.t;
select count(1)cts from emailtask_logs el where createtime >'v_start' and createtime <='v_end' and status=0;
";
            var sql = sqltemp.Replace("v_start", $"{start:yyyy-MM-dd}").Replace("v_end", $"{end:yyyy-MM-dd}");

            using (IDbConnection dbConnection = _PgConnection.PgConnection)
            {
                DataSet ds = new DataSet();
                var reader = dbConnection.ExecuteReader(sql);
       
                ds.Load(reader, LoadOption.PreserveChanges, new string[] { "datatables", "cts" });
                var _c = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                return Tuple.Create(_c, ds.Tables[0]);
            }
        }

        #endregion




    }
}
